In [1]:
import pandas as pd
import numpy as np
import plotly as px

Determine Top 10 Driver - Constructor Pair¶

In [2]:
results=pd.read_csv("F1_data/results.csv")
display(results)
resultId raceId driverId constructorId number grid position positionText positionOrder points laps time milliseconds fastestLap rank fastestLapTime fastestLapSpeed statusId
0 1 18 1 1 22 1 1 1 1 10.0 58 1:34:50.616 5690616 39 2 1:27.452 218.300 1
1 2 18 2 2 3 5 2 2 2 8.0 58 +5.478 5696094 41 3 1:27.739 217.586 1
2 3 18 3 3 7 7 3 3 3 6.0 58 +8.163 5698779 41 5 1:28.090 216.719 1
3 4 18 4 4 5 11 4 4 4 5.0 58 +17.181 5707797 58 7 1:28.603 215.464 1
4 5 18 5 1 23 3 5 5 5 4.0 58 +18.014 5708630 43 1 1:27.418 218.385 1
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
25655 25661 1086 825 210 20 13 16 16 16 0.0 69 \N \N 37 15 1:23.511 188.856 11
25656 25662 1086 848 3 23 17 17 17 17 0.0 69 \N \N 43 12 1:23.047 189.911 11
25657 25663 1086 849 3 6 19 18 18 18 0.0 69 \N \N 60 8 1:22.478 191.221 11
25658 25664 1086 852 213 22 16 19 19 19 0.0 68 \N \N 58 16 1:23.538 188.795 12
25659 25665 1086 822 51 77 8 20 20 20 0.0 65 \N \N 60 19 1:24.002 187.752 131

25660 rows × 18 columns

In [3]:
drivers_constructor = pd.DataFrame(results.groupby(['driverId','constructorId'],as_index=False)['points'].sum())
display(drivers_constructor)
driverId constructorId points
0 1 1 913.0
1 1 131 3395.5
2 2 2 163.0
3 2 3 28.0
4 2 4 34.0
... ... ... ...
2127 851 3 0.0
2128 852 213 43.0
2129 853 210 0.0
2130 854 210 12.0
2131 855 51 5.0

2132 rows × 3 columns

In [4]:
Top_10=drivers_constructor.nlargest(10, ['points'])
display(Top_10)
Top10_renamed = Top_10.rename(columns={'points':'Points'})
display(Top10_renamed)
driverId constructorId points
1 1 131 3395.5
2090 830 9 1730.5
71 20 9 1577.0
9 3 131 1519.0
70 20 6 1400.0
2075 822 131 1320.0
12 4 6 1190.0
23 8 6 1080.0
105 30 6 1066.0
55 17 9 978.5
driverId constructorId Points
1 1 131 3395.5
2090 830 9 1730.5
71 20 9 1577.0
9 3 131 1519.0
70 20 6 1400.0
2075 822 131 1320.0
12 4 6 1190.0
23 8 6 1080.0
105 30 6 1066.0
55 17 9 978.5
In [5]:
driver = pd.read_csv("drivers.csv")
driver["Driver_Name"] = driver["forename"] +" "+ driver["surname"]
display(driver)
driverId driverRef number code forename surname dob nationality url Driver_Name
0 1 hamilton 44 HAM Lewis Hamilton 1985-01-07 British http://en.wikipedia.org/wiki/Lewis_Hamilton Lewis Hamilton
1 2 heidfeld \N HEI Nick Heidfeld 1977-05-10 German http://en.wikipedia.org/wiki/Nick_Heidfeld Nick Heidfeld
2 3 rosberg 6 ROS Nico Rosberg 1985-06-27 German http://en.wikipedia.org/wiki/Nico_Rosberg Nico Rosberg
3 4 alonso 14 ALO Fernando Alonso 1981-07-29 Spanish http://en.wikipedia.org/wiki/Fernando_Alonso Fernando Alonso
4 5 kovalainen \N KOV Heikki Kovalainen 1981-10-19 Finnish http://en.wikipedia.org/wiki/Heikki_Kovalainen Heikki Kovalainen
... ... ... ... ... ... ... ... ... ... ...
849 851 aitken 89 AIT Jack Aitken 1995-09-23 British http://en.wikipedia.org/wiki/Jack_Aitken Jack Aitken
850 852 tsunoda 22 TSU Yuki Tsunoda 2000-05-11 Japanese http://en.wikipedia.org/wiki/Yuki_Tsunoda Yuki Tsunoda
851 853 mazepin 9 MAZ Nikita Mazepin 1999-03-02 Russian http://en.wikipedia.org/wiki/Nikita_Mazepin Nikita Mazepin
852 854 mick_schumacher 47 MSC Mick Schumacher 1999-03-22 German http://en.wikipedia.org/wiki/Mick_Schumacher Mick Schumacher
853 855 zhou 24 ZHO Guanyu Zhou 1999-05-30 Chinese http://en.wikipedia.org/wiki/Guanyu_Zhou Guanyu Zhou

854 rows × 10 columns

In [6]:
driver = driver[["driverId", "Driver_Name"]]
display(driver)
driverId Driver_Name
0 1 Lewis Hamilton
1 2 Nick Heidfeld
2 3 Nico Rosberg
3 4 Fernando Alonso
4 5 Heikki Kovalainen
... ... ...
849 851 Jack Aitken
850 852 Yuki Tsunoda
851 853 Nikita Mazepin
852 854 Mick Schumacher
853 855 Guanyu Zhou

854 rows × 2 columns

In [7]:
Join_driver = Top10_renamed.merge(driver, on='driverId', how='left')
display(Join_driver)
driverId constructorId Points Driver_Name
0 1 131 3395.5 Lewis Hamilton
1 830 9 1730.5 Max Verstappen
2 20 9 1577.0 Sebastian Vettel
3 3 131 1519.0 Nico Rosberg
4 20 6 1400.0 Sebastian Vettel
5 822 131 1320.0 Valtteri Bottas
6 4 6 1190.0 Fernando Alonso
7 8 6 1080.0 Kimi Räikkönen
8 30 6 1066.0 Michael Schumacher
9 17 9 978.5 Mark Webber
In [10]:
constructor = pd.read_csv("F1_data/constructors.csv")
constructor = constructor[["constructorId", "name"]]
display(constructor)
constructorId name
0 1 McLaren
1 2 BMW Sauber
2 3 Williams
3 4 Renault
4 5 Toro Rosso
... ... ...
206 209 Manor Marussia
207 210 Haas F1 Team
208 211 Racing Point
209 213 AlphaTauri
210 214 Alpine F1 Team

211 rows × 2 columns

In [11]:
Join_constructor = Join_driver.merge(constructor, on='constructorId', how='left')
display(Join_constructor)
Join_constructor_renamed = Join_constructor.rename(columns={'name':'Constructor_name'})
display(Join_constructor_renamed)
driverId constructorId Points Driver_Name name
0 1 131 3395.5 Lewis Hamilton Mercedes
1 830 9 1730.5 Max Verstappen Red Bull
2 20 9 1577.0 Sebastian Vettel Red Bull
3 3 131 1519.0 Nico Rosberg Mercedes
4 20 6 1400.0 Sebastian Vettel Ferrari
5 822 131 1320.0 Valtteri Bottas Mercedes
6 4 6 1190.0 Fernando Alonso Ferrari
7 8 6 1080.0 Kimi Räikkönen Ferrari
8 30 6 1066.0 Michael Schumacher Ferrari
9 17 9 978.5 Mark Webber Red Bull
driverId constructorId Points Driver_Name Constructor_name
0 1 131 3395.5 Lewis Hamilton Mercedes
1 830 9 1730.5 Max Verstappen Red Bull
2 20 9 1577.0 Sebastian Vettel Red Bull
3 3 131 1519.0 Nico Rosberg Mercedes
4 20 6 1400.0 Sebastian Vettel Ferrari
5 822 131 1320.0 Valtteri Bottas Mercedes
6 4 6 1190.0 Fernando Alonso Ferrari
7 8 6 1080.0 Kimi Räikkönen Ferrari
8 30 6 1066.0 Michael Schumacher Ferrari
9 17 9 978.5 Mark Webber Red Bull
In [12]:
import plotly.express as px
fig = px.bar(Join_constructor_renamed, x="Points", y="Driver_Name",
             color='Constructor_name',orientation='h',facet_row_spacing = 1,facet_col_spacing=1,color_discrete_sequence=["cornflowerblue", "lightcoral","goldenrod"],
            title='Top 10 Pair of Constructor & Driver')
fig.update_traces(width=0.5)
fig.update_layout(yaxis={'categoryorder':'total ascending'})
#fig.update_yaxes(tickangle= -75) 
fig.show()
In [ ]: